﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
/*
	Todo:
	Adaptions for modify
*/
namespace SqlServerDriver
{
	public partial class ModifyColumnForm : Form
	{
		public ModifyColumnForm(String tableName, SqlTableSchema schema, DbView.FieldInfo fi)
		{
			InitializeComponent();
			IsAdding = (fi.Name.Length == 0);

			this.fieldInfo = fi;
			this.fieldInfoOriginal = fi.Clone() as DbView.FieldInfo;
			this.schema = schema;
			this.tableName = tableName;
			// load up the controls
			SetHeading(tableName);
			InitialiseTypeCategories();
			LoadControls();
		}

		public static DbView.FieldInfo NewField()
		{
			DbView.FieldInfo fi = new DbView.FieldInfo("", "", 0);
			return fi;
		}

		public String GenerateScript(bool isAltScriptWanted)
		{
			// bare alter table command. May be enough
			String script = String.Format("alter table {0} add {1}", this.tableName, FieldScripter.ScriptField(this.fieldInfo, this.tableName));
			// if adding the column it should be enough
			if (IsAdding) return script;

			// doesn't work with a constraint.
			// drop old name
			String df = FieldScripter.MakeDefaultName(this.fieldInfoOriginal, this.tableName);
			String dropConstraint = (df.Length == 0) ? "" :  String.Format("alter table {0} drop constraint {1}", Helper.SafeTableName(this.tableName), df);
			// add new if necessary
			df = FieldScripter.MakeDefaultName(this.fieldInfo, this.tableName);
			String addConstraint = (df.Length == 0) ? "" : String.Format("\r\nalter table {0} add constraint {1} default {2} for {3}",  
															Helper.SafeTableName(this.tableName), df, 
															this.fieldInfo.ConstraintDefinition, 
															Helper.SafeSqlField(this.fieldInfo.Name));
			// disable constraint for cor generation
			this.fieldInfo.HasDefault = false;
			// rename column if necessary
			String rename = (this.fieldInfo.Name != this.fieldInfoOriginal.Name) ? String.Format("exec sp_RENAME '{0}.{1}', '{2}'\r\n", this.tableName, this.fieldInfoOriginal.Name, this.fieldInfo.Name) : "";

			script = dropConstraint + "\r\n" +
					 rename +"\r\n" +
		  			 String.Format("alter table {0} alter column {1}\r\n", Helper.SafeTableName(this.tableName), FieldScripter.ScriptField(this.fieldInfo, this.tableName)) +
					 addConstraint;

			if (df.Length > 0)
			{
				this.fieldInfo.HasDefault = true;
			}

			// 1st time in try the easy way
			if (!isAltScriptWanted)
				return script;

			// assemble data for the big template
			// 
			SqlScriptingHelper scriptHelper = new SqlScriptingHelper("tgt", this.schema);
			String keyCompare = scriptHelper.ScriptKeyCompare("h");
			String keyList =  scriptHelper.ScriptKeyList("");
			// bits 
			
			// hack because even if here therename part of the previous script has executed.
			// this is not good on so many levels. So in future it should rollback at which point
			// we need to replace below with the commented out version
			String oldName = this.fieldInfo.Name;
			String holdScript = String.Format(ALT_MODIFY_HOLDDATA, Helper.SafeTableName(this.tableName), Helper.SafeSqlField(oldName), this.fieldInfo.SafeName, this.fieldInfo.SqlType, keyList);
			String holdScriptResize = String.Format(ALT_MODIFY_HOLDDATA_RESIZE, Helper.SafeTableName(this.tableName), Helper.SafeSqlField(oldName), this.fieldInfo.SafeName, this.fieldInfo.SqlType, this.fieldInfo.Length, keyList);
			String changeColumnScript = String.Format(ALT_MODIFY_NOKEEPDATA, Helper.SafeTableName(this.tableName), Helper.SafeSqlField(oldName), this.fieldInfo.SqlType,
													GenerateAddColumnScript(),
													dropConstraint,
													keyList,
													keyCompare);
			String restoreScript = String.Format(ALT_MODIFY_RESTORE_DATA, Helper.SafeTableName(this.tableName), this.fieldInfo.SafeName, keyCompare);
			
			if (this.fieldInfo.IsCalc)
			{
				// if calc then data will be recalculated anyway
				script = changeColumnScript;
			}
			else
			{
				script = (this.fieldInfo.Type == "C" && (this.fieldInfoOriginal.Length > this.fieldInfo.Length)) ? holdScriptResize : holdScript;
				script += changeColumnScript + restoreScript;
			}
			return script;
		}
		// determine the effect of the alt script on the data
		// ie truncate/clear/move 
		public String AltEffect()
		{
			String effect = String.Format("Column {0} will be cleared of data and moved to the end of the column list", this.fieldInfo.Name);
			if (this.fieldInfoOriginal.Type == "C")
				if (this.fieldInfoOriginal.Length > this.fieldInfo.Length)
					effect = String.Format("Column {0} will be moved to the end of the column list and some data may be truncated", this.fieldInfo.Name);
			return effect;
		}

		private String GenerateAddColumnScript()
		{
			String script = String.Format("alter table {0} add {1}", this.tableName, FieldScripter.ScriptField(this.fieldInfo, this.tableName));
			return script;
		}

		private void ModifyColumnForm_Load(object sender, EventArgs e)
		{
			if (!IsAdding)
			{
				comboSqlType.Select();
				// freeze other types on modify
				RadioButton c = radioDate; c.Enabled = c.Checked;
				c = radioNumber; c.Enabled = c.Checked;
				c = radioOther; c.Enabled = c.Checked;
				//c = radioString; c.Enabled = c.Checked;
			}
		}
		
		private void BtnCancel_Click(object sender, EventArgs e)
		{
			this.DialogResult = System.Windows.Forms.DialogResult.Cancel;
			this.Close();
		}

		private void BtnSave_Click(object sender, EventArgs e)
		{
			// validate & transfer control info to fieldinfo
			SaveControls();
			if (ValidatesOk())
			{
				// a slow op will be a computed or default column with no-nulls
				if (!CheckCancelSlowOp())
				{
					this.DialogResult = System.Windows.Forms.DialogResult.OK;
					this.Close();
				}
			}
		}

		private void BtnScript_Click(object sender, EventArgs e)
		{
			SaveControls();
			if (ValidatesOk())
			{
				String script = GenerateScript(false);
				ShowSourceForm displayForm = new ShowSourceForm(this.fieldInfo.Name, script, "");
				displayForm.ShowDialog(this);
			}
		}

		private void Radio_CheckedChanged(object sender, EventArgs e)
		{
			RadioButton rb = sender as RadioButton;
			if (rb != null) 
				if (rb.Checked)
					SetFieldCombo(rb, 0);
		}

		private void ComboSqlType_SelectedIndexChanged(object sender, EventArgs e)
		{
			OnChangeType();
		}
		
		void OnChangeType()
		{
			bool enableLength = false;
//			bool isDecimalSelected = (comboSqlType.SelectedItem.ToString() == SQL_DECIMAL_TYPE);
			bool isDecimalSelected = LengthIsDp(comboSqlType.SelectedItem.ToString());
			if (radioString.Checked || isDecimalSelected) 
				enableLength = true;
			txtLength.Enabled = enableLength;
			lblLength.Enabled = enableLength;
			if (radioNumber.Checked)
				lblLength.Text = "Dp";
			else 
				lblLength.Text = "&Length";
		}
		private void TxtDefault_TextChanged(object sender, EventArgs e)
		{
			ToggleWarning();
		}

		////////////////////////////////////////////////////////////////////////////////////
		// real work
		//
		private void SetHeading(String tableName)
		{
			if (IsAdding)
				lblHeading.Text = String.Format("Add column to {0}", tableName);
			else
				lblHeading.Text = String.Format("Modify column {1} in {0}", tableName, this.fieldInfo.Name);
		}

		public bool IsAdding {get; set; }
		
		void LoadControls()
		{
			txtName.Text = this.fieldInfo.Name;
			txtComputed.Text = (this.fieldInfo.IsCalc) ? CheckStripBrackets(this.fieldInfo.ConstraintDefinition) : "";
			txtDefault.Text = (this.fieldInfo.HasDefault) ? CheckStripBrackets(this.fieldInfo.ConstraintDefinition) : "";
			int selection;
			RadioButton control = SetRadioSelectionFromFieldInfo(out selection);
			control.Checked = true;
			SetFieldCombo(control, selection);
			comboNulls.Items.Add("Yes");
			comboNulls.Items.Add("No");
			comboNulls.SelectedIndex = (this.fieldInfo.AllowsNulls) ? 0 : 1;
			if (radioString.Checked)
				txtLength.Text = (this.fieldInfo.Length == -1) ? "MAX" : 
								 (this.fieldInfo.Length == 0) ? "":
								this.fieldInfo.Length.ToString();
			else
				if (LengthIsDp(this.fieldInfo.SqlType))
//				if (this.m_FieldInfo.SqlType == SQL_DECIMAL_TYPE)
					txtLength.Text = this.fieldInfo.Dp.ToString();
			if (!IsAdding)
			{
				//comboOnTruncate.SelectedIndex = 0;
				//comboOnError.SelectedIndex = 0;
				// can't rename 
				// you can? txtName.Enabled = false;
				comboSqlType.Focus();
			}
			else // disable options
			{
				//groupModOptions.Enabled = false;
				//checkClearData.Enabled = false;
				//lblOnError.Enabled = false;
				//comboOnTruncate.Enabled = false;
				//lblOnTruncate.Enabled = false;
				//comboOnError.Enabled = false;
			}
		}

		private string CheckStripBrackets(string data)
		{
			if (data.Length < 2) return data; // can't be
			if (data[data.Length - 1] != ')') return data; // doesn't end in )
			if (data[0] == '(') 
				return data.Substring(1, data.Length - 2);
			return data;
		}

		void SaveControls()
		{
			this.fieldInfo.Name = txtName.Text;
			this.fieldInfo.HasDefault = false;
			this.fieldInfo.IsCalc = false;
			if (txtDefault.Text.Length > 0)
			{
				this.fieldInfo.ConstraintDefinition = txtDefault.Text;
				this.fieldInfo.HasDefault = true;
			}
			else
				if (txtComputed.Text.Length > 0)
				{
					this.fieldInfo.ConstraintDefinition = txtComputed.Text;
					this.fieldInfo.IsCalc = true;
				}
			this.fieldInfo.SqlType = comboSqlType.SelectedItem.ToString();
			this.fieldInfo.AllowsNulls = (comboNulls.SelectedIndex == 1) ? false : true;
			if (txtLength.Text.ToUpper() == "MAX")
			{
				this.fieldInfo.Length = -1;
			}
			else
			{
				int fieldLength;
				if (LengthIsDp(this.fieldInfo.SqlType))
				//if (this.m_FieldInfo.SqlType == SQL_DECIMAL_TYPE)
				{
					if (int.TryParse(txtLength.Text, out fieldLength) == false)
						this.fieldInfo.Dp =  0;
					else
						this.fieldInfo.Dp =  fieldLength;
					if (IsAdding)  // otherwise keep the existing length
						this.fieldInfo.Length = DEFAULT_DECIMAL_LENGTH; 
				}
				else
				{
					if (int.TryParse(txtLength.Text, out fieldLength) == false)
						this.fieldInfo.Length = INVALID_FIELD_LENGTH;
					else
						this.fieldInfo.Length = fieldLength;
				}
			}
		}

		bool ValidatesOk()
		{
			if (txtName.Text.Trim().Length == 0)
				return ValidationError("You forgot to enter a name for the new column!", txtName);

			bool lenReqd = (this.fieldInfo.SqlType.ToUpper().IndexOf("CHAR") != -1);
			if (lenReqd)
				if (this.fieldInfo.Length == INVALID_FIELD_LENGTH)
					return ValidationError("String types need a valid length", txtLength);

			// apparently not.
			//if (!m_FieldInfo.AllowsNulls)
			//    if (!m_FieldInfo.IsCalc && !m_FieldInfo.HasDefault)
			////        return ValidationError("Adding a non-null column requires it either to be computed or to have a default value ", txtDefault);

			return true;
		}

		private bool ValidationError(string message, Control focusControl)
		{
			MessageBox.Show(message, "Incorrect entry", MessageBoxButtons.OK, MessageBoxIcon.Error);
			focusControl.Focus();
			return false; // ie fails validation
		}

		private bool CheckCancelSlowOp()
		{
			if (!this.fieldInfo.HasDefault || this.fieldInfo.AllowsNulls)
				return false; // not slow 
			String warningReason = "Column has a default value and does not allow nulls";
			// potential slow operation
			String op = (IsAdding) ? "Add" : "Modify";
			String warning = String.Format(
@"{1}ing this field will involve updating all previous rows. 
The reason is: [ {0} ]
For a large table this may take some time. Please be patient. 
Please confirm you still wish to proceed to {1} the column", warningReason, op);
			DialogResult dr = System.Windows.Forms.MessageBox.Show(this, warning, op, System.Windows.Forms.MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Question);
			return (dr) == System.Windows.Forms.DialogResult.No ? true : false;
		}

		private void SetFieldCombo(RadioButton radio, int selection)
		{
			comboSqlType.Items.Clear();
			List<String> types = this.typeMap[radio];
			foreach (String type in types)
				comboSqlType.Items.Add(type);
			comboSqlType.SelectedIndex = selection;
		}

		private RadioButton SetRadioSelectionFromFieldInfo(out int selection)
		{
			// new field make varchar
			if (this.fieldInfo.SqlType.Length == 0)
			{
				selection = 0;
				return radioString;
			}

			RadioButton rb = radioString;
			selection = SelectionIndexOf(this.fieldInfo.SqlType, rb);
			if (selection != -1) return rb;
			rb = radioNumber;
			selection = SelectionIndexOf(this.fieldInfo.SqlType, rb);
			if (selection != -1) return rb;
			rb = radioDate;
			selection = SelectionIndexOf(this.fieldInfo.SqlType, rb);
			if (selection != -1) return rb;
			rb = radioOther;
			selection = SelectionIndexOf(this.fieldInfo.SqlType, rb);
			return rb;
		}

		private int SelectionIndexOf(string p, RadioButton radio)
		{
			List<String> types = this.typeMap[radio];
			for (int idx = 0; idx < types.Count; ++idx)
			{
				if (types[idx].ToLower() == p.ToLower()) return idx;
			}
			return -1;
		}
		
		private void InitialiseTypeCategories()
		{
			this.typeMap.Add(radioString, new List<String>());
			PopulateList(this.typeMap[radioString], "varchar,char,nvarchar,nchar"); //could add ,text,ntext but these are rubbish
			
			this.typeMap.Add(radioNumber, new List<String>());
			PopulateList(this.typeMap[radioNumber], "int,"+SQL_NUMERIC_TYPE+",float,bigint,smallint,tinyint,"+SQL_DECIMAL_TYPE+",money,real");

			this.typeMap.Add(radioDate, new List<String>());
			PopulateList(this.typeMap[radioDate], "datetime,date");

			this.typeMap.Add(radioOther, new List<String>());
			PopulateList(this.typeMap[radioOther], "bit,xml,uniqueidentifier");
		}

		private void PopulateList(List<string> list,string csv)
		{
			String[] items = csv.Split(',');
			foreach (String item in items)
				list.Add(item);
		}
	
		private void ToggleWarning()
		{
			bool wantedWarningState = false;
			if (txtDefault.Text.Length > 0)
				if (radioString.Checked) wantedWarningState = true;
			if (wantedWarningState != this.lblWarning.Visible) lblWarning.Visible = wantedWarningState;
		}

		// detect when to use the length entry as the Dp entry. 
		private bool LengthIsDp(String sqlType)
		{
			// may be needed with money one day.
			return (sqlType == SQL_DECIMAL_TYPE) ? true :
					(sqlType == SQL_NUMERIC_TYPE) ? true : false;
		}

		const int INVALID_FIELD_LENGTH = -9;
		// navigating between ease and utility, below defines some defaults for a decimal type
		// length 15 is good for I think 999,000,000,000,000. 
		const String SQL_DECIMAL_TYPE = "decimal";
		const String SQL_NUMERIC_TYPE = "numeric";
		const int DEFAULT_DECIMAL_LENGTH = 15;
		//
		String tableName;
		SqlTableSchema schema;
		DbView.FieldInfo fieldInfo;
		DbView.FieldInfo fieldInfoOriginal;
		// 
		Dictionary<RadioButton, List<String>> typeMap = new Dictionary<RadioButton,List<string>>();

// 0 = table
// 1 = old field
// 2 = new type
// 3 = Add script
// 4 = Drop constraint
// 5 = pk field list
// 6 = pk field match h.F1 = tgt.F1
//        const String ALT_MODIFY = @"
//-- backup column 
//if object_id('tempdb..#holddata') is not null drop table #holddata
//
//select {5}, convert({2}, {1}) as {1}
//  into #holddata
//  from {0}
//
//-- remove old add new
//{4}
//alter table {0} drop column [{1}]
//{3} -- alter table {0} add [{1}] {2}
//
//-- refresh data
//update tgt set {1} = h.{1}
//from {0} tgt
//join #holddata h on {6}
//";
// 0 = table
// 1 = old field
// 2 = new type
// 3 = Add script
// 4 = Drop constraint
		const String ALT_MODIFY_NOKEEPDATA = @"
-- remove old add new
{4}
alter table {0} drop column {1}
{3} -- alter table {0} add {1} {2}
";

// 0 = table
// 1 = old field
// 2 = new field
// 3 = new type
// 4 = pk field list
		const String ALT_MODIFY_HOLDDATA = @"
-- backup column 
if object_id('tempdb..#holddata') is not null drop table #holddata

select {4}, convert({3}, {1}) as {2}
  into #holddata
  from {0}
";
// 0 = table
// 1 = old field
// 2 = new field
// 3 = new type
// 4 = new length
// 5 = pk field list
		const String ALT_MODIFY_HOLDDATA_RESIZE = @"
-- backup column 
if object_id('tempdb..#holddata') is not null drop table #holddata

select {5}, convert({3}({4}), Left({1},{4})) as {2}
  into #holddata
  from {0}
";

// 0 = table
// 1 = new field
// 2 = pk field match h.F1 = tgt.F1
//
		const String ALT_MODIFY_RESTORE_DATA = @"
-- refresh data
update tgt set {1} = h.{1}
from {0} tgt
join #holddata h on {2}
";
	}
}
